package com.meet.dao;

import com.meet.entity.Meeting;
import com.meet.entity.Workers;
import com.meet.util.DateUtil;
import com.meet.util.JDBCutil;
import com.meet.vo.MeetingConfirm;
import com.meet.vo.MeetingVo;
import com.meet.vo.WorkersVo;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @Author LiDianyang
 * @Date 2022/8/11 21:08
 * @PackageName:com.meet.dao
 * @ClassName: MeetingDaoImpl
 * @Description: TODO
 * @Version 1.0
 */
public class MeetingDaoImpl implements MeetingDao{


    @Override
    public int addMeeting(Meeting meeting) throws SQLException {
        //-------------
        Date date= DateUtil.getCurrentTime();
        Timestamp thistime=new Timestamp(date.getTime());
        String dateStr=date.toString();
        //System.out.println(dateStr);
        int index= dateStr.indexOf(":");
       // System.out.println(index);
        String mytime=dateStr.substring(index-2,index+6);
        mytime="%"+mytime+"%";
       // System.out.println("+++"+mytime);

        //-------拿到当前的时间，用来查询这条插入的语句的id
        Connection connection = JDBCutil.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement("insert into meeting (m_name,r_id,t_start,t_end,w_id,t_reserve) values (?,?,?,?,?,?)");
        preparedStatement.setObject(1,meeting.getmName());
        preparedStatement.setObject(2,meeting.getrId());
        preparedStatement.setObject(3,meeting.gettStart());
        preparedStatement.setObject(4,meeting.gettEnd());
        preparedStatement.setObject(5,meeting.getwId());
        preparedStatement.setObject(6,thistime);

//        preparedStatement.setObject(7,meeting.getContent());
        int line = preparedStatement.executeUpdate();
        JDBCutil.closeConnection();
        //-----查询这条插入的id-----
        Connection con = JDBCutil.getConnection();
        PreparedStatement preparedStatement1 = con.prepareStatement("select id from meeting where t_reserve like ?");
        preparedStatement1.setObject(1,mytime);
        ResultSet resultSet=preparedStatement1.executeQuery();
        resultSet.next();
        int myid=resultSet.getInt("id");

        return myid;
    }

    public int updateMeeting(Meeting meeting) throws SQLException {

        Connection connection = JDBCutil.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement("update meeting set m_name=?,r_id=?,t_start=?,t_end=?,w_id=?,t_reserve=? where id=?  ");
        preparedStatement.setObject(1,meeting.getmName());
        preparedStatement.setObject(2,meeting.getrId());
        preparedStatement.setObject(3,meeting.gettStart());
        preparedStatement.setObject(4,meeting.gettEnd());
        preparedStatement.setObject(5,meeting.getwId());
        preparedStatement.setObject(6,meeting.gettReserve());
//        preparedStatement.setObject(7,meeting.getContent());
        preparedStatement.setObject(7,meeting.getId());
        int line = preparedStatement.executeUpdate();
        JDBCutil.closeConnection();
        return line;
    }


    public int deleteMeeting(Integer id)  {

        int line=0;
        Connection connection = JDBCutil.getConnection();

        try{
            PreparedStatement preparedStatement = connection.prepareStatement("delete from meeting  where id=? ");
            preparedStatement.setObject(1,id);
            line=preparedStatement.executeUpdate();

            PreparedStatement preparedStatement1 = connection.prepareStatement("delete from user_meeting where meeting_id =?");
            preparedStatement1.setObject(1,id);
            preparedStatement1.executeUpdate();
            JDBCutil.closeConnection();

        }catch (SQLException throwables) {
            try {
                connection.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            throwables.printStackTrace();
        }


        return line;

    }
    public List<MeetingVo> getMeetingCard(int page, int number) throws SQLException {

        List<MeetingVo> list = new ArrayList<MeetingVo>();

        Connection connection = null;
        connection = JDBCutil.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement("SELECT m.id, m.m_name, b.r_name, m.t_start, m.t_end, w.u_name, m.t_reserve from meeting m, workers w, boardroom b WHERE m.r_id=b.id and m.w_id=w.id  limit ?,?");

        preparedStatement.setObject(1,page);
        preparedStatement.setObject(2,number);

        ResultSet resultSet = preparedStatement.executeQuery();
        while(resultSet.next()){
            MeetingVo meeting = new MeetingVo();
            meeting.setId(resultSet.getInt("id"));
            meeting.setmName(resultSet.getString("m_name"));
            meeting.setrName(resultSet.getString("r_name"));
            meeting.settStart(resultSet.getTimestamp("t_start"));
            meeting.settEnd(resultSet.getTimestamp("t_end"));
            meeting.setwName(resultSet.getString("u_name"));
            meeting.settReserve(resultSet.getTimestamp("t_reserve"));
//            meeting.setContent(resultSet.getString("content"));

            list.add(meeting);
        }

        resultSet.close();
        JDBCutil.closeConnection();
        return list;
    }

    public int getCount() throws SQLException {
        Connection connection = JDBCutil.getConnection();
        //发送sql语句
        PreparedStatement preparedStatement = connection.prepareStatement("select count(*) from meeting");
        ResultSet resultSet = preparedStatement.executeQuery();
        int count=0;
        if(resultSet.next()){
            count=resultSet.getInt(1);
        }else{
            throw new SQLException("表中没有数据！");
        }
        return count;
    }

    @Override
    public MeetingVo getMeeting(Integer Id) throws SQLException {
        Connection connection = JDBCutil.getConnection();
        //发送sql语句
        PreparedStatement preparedStatement = connection.prepareStatement(" SELECT m.* , b.r_name from meeting m, boardroom b WHERE m.r_id=b.id and m.id=? ");
        preparedStatement.setObject(1, Id);
        ResultSet resultSet = preparedStatement.executeQuery();
        MeetingVo meeting = new MeetingVo();
      if(resultSet.next())  {
        meeting.setmName(resultSet.getString("m_name"));
            meeting.setId(resultSet.getInt("id"));
            meeting.setrName(resultSet.getString("r_name"));
            meeting.setrId(resultSet.getInt("r_id"));
            meeting.settStart(resultSet.getTimestamp("t_start"));
            meeting.settEnd(resultSet.getTimestamp("t_end"));
            meeting.setwId(resultSet.getInt("w_id"));
            meeting.settReserve(resultSet.getTimestamp("t_reserve"));
//            meeting.setContent(resultSet.getString("8"));
        }else{
            throw new SQLException("表中没有数据！");
        }
        return meeting;
    }

    /**
     * @return
     * @throws SQLException
     * @author King_of_hehe
     */
    @Override
    public List<Meeting> getAll() throws SQLException {

        List<Meeting> list = new ArrayList<>();
        Connection connection = JDBCutil.getConnection();
        String sql = "select * from meeting";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();

        while (resultSet.next()){

            Meeting meeting = new Meeting();
            meeting.setId(resultSet.getInt(1));
            meeting.setmName(resultSet.getString(2));

            list.add(meeting);


        }

        resultSet.close();
        JDBCutil.closeConnection();

        return list;
    }

    /**
     * 李滇阳加的
     * @param tStart
     * @param tEnd
     * @return
     * @throws SQLException
     */
    public List<Meeting> getAllMeeting(Integer id,Timestamp tStart, Timestamp tEnd) throws SQLException {


        List<Meeting> list = new ArrayList<Meeting>();
        Connection connection =JDBCutil.getConnection();
        String sql="select * from meeting where \n" +
                "(\n" +
                "   (? >= t_start and ?<=t_end )\n" +
                "\t\tor (?>=t_start and ?<=t_end))\n" +
                "\t\t\t\t\t\t\t\tand meeting.r_id=?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setObject(1,tStart);
        preparedStatement.setObject(2,tStart);
        preparedStatement.setObject(3,tEnd);
        preparedStatement.setObject(4,tEnd);
        preparedStatement.setObject(5,id);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()){

            Meeting meeting = new Meeting();
            meeting.setId(resultSet.getInt(1));
            meeting.setmName(resultSet.getString(2));
            meeting.setrId(resultSet.getInt(3));
            meeting.settStart(resultSet.getTimestamp(4));
            meeting.settEnd(resultSet.getTimestamp(5));
            meeting.setwId(resultSet.getInt(6));
            meeting.settReserve(resultSet.getTimestamp(7));
            list.add(meeting);
        }
        resultSet.close();
        JDBCutil.closeConnection();
        return list;
    }
/**
 * 曾广龙写的
 *
 */
@Override
public List<MeetingConfirm> getMeetingConfirm(boolean tag,Integer mid) throws SQLException {
    List<MeetingConfirm> lists = new ArrayList<MeetingConfirm>();

    Connection connection = null;
    connection = JDBCutil.getConnection();
    PreparedStatement preparedStatement = connection.prepareStatement("SELECT a.id,a.u_name,a.phonenumber,b.confirm from workers a,user_meeting b,meeting c WHERE a.id = b.user_id AND c.id=b.meeting_id and confirm=? and b.meeting_id = ?");
    preparedStatement.setObject(1,tag);
    preparedStatement.setObject(2,mid);

    ResultSet resultSet = preparedStatement.executeQuery();
    while(resultSet.next()){
        MeetingConfirm meeting = new MeetingConfirm();
        meeting.setId(resultSet.getInt(1));
        meeting.setName(resultSet.getString(2));
        meeting.setPhone(resultSet.getString(3));
        meeting.setConfirm(resultSet.getBoolean(4));
//            meeting.setContent(resultSet.getString("content"));

        lists.add(meeting);
    }

    resultSet.close();
    JDBCutil.closeConnection();
    return lists;

}

}
